﻿using System;
using System.Data;
using System.Data.SQLite;

namespace Server.Library.SqLite
{
    /// <summary>
    /// sqlite操作类
    /// </summary>
    public class SqLiteOperate
    {
        private bool _connectedStatus;
        /// <summary>
        /// 数据库连接状态
        /// </summary>
        public bool ConnectedStatus => _connectedStatus;

        private string _fileName;
        /// <summary>
        /// 数据库文件名
        /// </summary>
        public string FileName => _fileName;

        private SQLiteConnection _connection;
        //private SQLiteCommand _command;

        protected SqLiteOperate()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName">数据库连接字符串</param>
        public SqLiteOperate(string fileName)
        {
            _connection = new SQLiteConnection($"data source={fileName};Version=3;MultipleActiveResultSets=True;");
            //SQLiteCommand _command = new SQLiteCommand(_connection);
        }


        /// <summary>
        /// 连接
        /// </summary>
        /// <returns></returns>
        public bool Connected()
        {
            try
            {
                _connection.Open();
                _connectedStatus = true;
            }
            catch (Exception e)
            {
                throw new Exception($"sqlite数据库连接失败:{e.Message}");
            }

            return _connectedStatus;
        }


        public DataTable GetTable(string sql)
        {
            if (!_connectedStatus)
            {
                return null;
            }
            SQLiteCommand _command = new SQLiteCommand(_connection);
            _command.CommandText = sql;
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(_command);
            DataSet set = new DataSet();
            adapter.Fill(set);
            DataTable dataTable = set.Tables[0];
            return dataTable;
        }

        public int ExecuteNoQuery(string sql)
        {
            if (!_connectedStatus)
            {
                return -1;
            }
            SQLiteCommand _command = new SQLiteCommand(_connection);
            _command.CommandText = sql;
            var result = _command.ExecuteNonQuery();
            return result;
        }


        public object ExecuteScalar(string sql)
        {
            if (!_connectedStatus)
            {
                return null;
            }
            SQLiteCommand _command = new SQLiteCommand(_connection);
            _command.CommandText = sql;
            return _command.ExecuteScalar();
        }

        /// <summary>
        /// 断开连接
        /// </summary>
        /// <returns></returns>
        public bool DisConnect()
        {
            try
            {
                if (_connectedStatus)
                {
                    _connection.Close();
                    _connectedStatus = false;
                }
            }
            catch (Exception e)
            {
                throw new Exception($"SqLite数据库断开连接失败:{e.Message}");
            }
            return !_connectedStatus;
        }
    }
}
